﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
/*
	Todo
	- drop prop table and column
	- test table 
	- make it clear the drop script deletes all. Decide whether this is OK.
*/
namespace SqlServerDriver
{
	public class ExtendedPropertyScripting
	{
		// this method is fed by a datarow from a DataTable that queries the proprties.
		internal static void ScriptPropertyInfoLine(StringBuilder sb, DataRow dr, ScriptOption scriptOption)
		{
			// table is shown as schema.table. This needs to be broken up. Note: table is index 0, schema (if exists, is 1),
			String [] tableData = Helper.ParseTableName(dr[ExtendedPropertiesTemplates.COLUMN_INDEX_SCHEMA_TABLE].ToString());
			String prop = dr[ExtendedPropertiesTemplates.COLUMN_PROPERTY_NAME].ToString();
			String value = dr[ExtendedPropertiesTemplates.COLUMN_PROPERTY_VALUE].ToString();
			switch (scriptOption)
			{
				case ScriptOption.addTable:
					ScriptAddTableProperty(sb, tableData[1], tableData[0], prop, value);
					break;
				case ScriptOption.addColumn:
					ScriptAddTableColumnProperty(sb, tableData[1], tableData[0], dr[ExtendedPropertiesTemplates.COLUMN_TABLE_COLUMN].ToString(), prop, value);
					break;
				case ScriptOption.dropTable:
					sb.Append(String.Format("union select '{0}','{1}','{2}'\r\n", tableData[1], tableData[0], prop));
					break;
				case ScriptOption.dropColumn:
					sb.Append(String.Format("union select '{0}','{1}','{2}','{3}'\r\n", tableData[1], tableData[0], dr[ExtendedPropertiesTemplates.COLUMN_TABLE_COLUMN].ToString(), prop));
					break;
			}
		}
		
		public static void ScriptAddTableColumnProperty(StringBuilder sb, string schema, string table, string column, string propertyName, string propertyValue)
		{
			String line = String.Format("union select '{0}','{1}','{2}','{3}',N'{4}'\r\n", schema, table, column, propertyName, Helper.SafeData(propertyValue));
			sb.Append(line);
		}

		public static void ScriptAddTableProperty(StringBuilder sb, string schema, string table, string propertyName, string propertyValue)
		{
			String line = String.Format("union select '{0}','{1}','{2}',N'{3}'\r\n", schema, table, propertyName, Helper.SafeData(propertyValue));
			sb.Append(line);
		}

		public enum ScriptOption {addTable, addColumn, dropTable, dropColumn};
		public static String MakeScript(StringBuilder sb, ScriptOption option)
		{
			string template =	(option == ScriptOption.addTable) ? AddTablePropertiesScriptTemplate :
								(option == ScriptOption.addColumn) ? AddTableColumnPropertiesScriptTemplate : 
								(option == ScriptOption.dropTable) ? DropTablePropertyScriptTemplate : 
								(option == ScriptOption.dropColumn) ? DropTableColumnPropertyScriptTemplate : "";
			String data = sb.ToString();
			// trim initial union
			if (data.IndexOf("union") == 0)
				data = data.Substring(5);
			return String.Format(template, data);
		}
		
		const String AddTableColumnPropertiesScriptTemplate = @"
-- create working table
if object_id('tempdb..#props') is not null drop table #props
create table #props (rowid int identity(1,1), sch varchar(128), tbl varchar(128), col  varchar(128), prop nvarchar(128), val nvarchar(4000), prev_val nvarchar(4000), row_state int)

-- populate with properties data to apply.
insert into #props (sch, tbl, col, prop, val)
{0}

-- detect missing tables and columns and save previous values.
update p set prev_val = convert(nvarchar(4000), SEP.value),
			row_state =	case when TBL.name is null or SCH.name is null then -1  -- table not found
						     when COL.name is null then -2						-- column not found
							 else 0 end
  from #props p
  left JOIN sys.tables TBL ON p.tbl = TBL.name
  left join sys.schemas SCH ON p.sch = SCH.name
  left join sys.columns COL ON p.col = COL.name and COL.OBJECT_ID = TBL.object_id
  left join sys.extended_properties SEP on SEP.major_id = COL.OBJECT_ID AND SEP.minor_id = COL.column_id 
								  and SEP.CLASS = 1 AND (SEP.value <> '1' AND SEP.value <> 1)
								  and SEP.name = prop

-- calculate the state of each update 
update #props set row_state = case when prev_val is null then 1  -- adding
								  when val != prev_val then 2	 -- updateing existing
								  when val = prev_val then 0	 -- no change
								  else 0 end					 -- dunno
 where row_state = 0

--
-- exec loop
--
declare @s varchar(128), @t varchar(128), @c  varchar(128), @p  nvarchar(128), @v nvarchar(4000), @r int
declare @index int

-- alternative to a cursor
select @index = min(rowid) from #props where row_state in (1,2) -- only those flagged for add or update
while (@index is not null)
begin
	-- get next record to process
	select top 1 @s = sch, @t = tbl, @c = col, @p = prop, @v = val, @r = row_state 
	 from #props 
	where rowid = @index 
	
	if @r = 1
		EXEC sys.sp_addextendedproperty 
				@name = @p, 
				@value = @v,
				@level0type = N'SCHEMA', @level0name = @s, 
				@level1type = N'TABLE',  @level1name = @t,
				@level2type = N'COLUMN', @level2name = @c;
	if @r = 2
		EXEC sys.sp_updateextendedproperty
				@name = @p, 
				@value = @v,
				@level0type = N'SCHEMA', @level0name = @s, 
				@level1type = N'TABLE',  @level1name = @t,
				@level2type = N'COLUMN', @level2name = @c;

	-- find then next row to process. If none found then  @index = null will terminate the loop
	select @index = min(rowid) from #props where rowid > @index and row_state in (1,2)
end 
--
-- Report
--
select case row_state when -2 then 'Column not Found' 
					  when -1 then 'Table not Found' 
					  when 0 then 'No change to value' 
					  when 1 then 'Added property'
					  when 2 then 'Updated property' else 'Unknown' end as Status,
					  count(row_state) as Number
  from #props
group by row_state

if exists (select 1 from #props where row_state = 2)
	select sch + '.' + tbl as table_name, col as column_name, prop as property,prev_val as old_value, val as new_value 
	  from #props 
	  where row_state = 2
";

		const String AddTablePropertiesScriptTemplate = @"

if object_id('tempdb..#props') is not null drop table #props

create table #props (rowid int identity(1,1), sch varchar(128), tbl varchar(128), col  varchar(128), 
											prop nvarchar(128), val nvarchar(4000), 
											prev_val nvarchar(4000), row_state int)
insert into #props (sch, tbl, prop, val)
-- insert rows here
{0}

-- populate with existing data
update p set prev_val = convert(nvarchar(4000), SEP.value),
			row_state =	case when TBL.name is null or SCH.name is null then -1
							 else 0 end
  from #props p
  left join sys.schemas SCH ON p.sch = SCH.name
  left JOIN sys.tables TBL ON p.tbl = TBL.name and TBL.schema_id = SCH.schema_id
  left join sys.extended_properties SEP on  SEP.major_id = TBL.OBJECT_ID
								  and SEP.CLASS = 1 AND SEP.minor_id = 0 AND (SEP.value <> '1' AND SEP.value <> 1)
								  and SEP.name = prop
update #props set row_state = case when prev_val is null then 1  -- adding
								  when val != prev_val then 2	 -- updateing existing
								  when val = prev_val then 0	 -- no change
								  else 0 end					 -- dunno
 where row_state = 0

--
-- exec loop
--
declare @s varchar(128), @t varchar(128), @p  nvarchar(128), @v nvarchar(4000), @r int
declare @index int

select @index = min(rowid) from #props where row_state in (1,2) -- only those flagged for add or update
while (@index is not null)
begin
	select top 1 @s = sch, @t = tbl, @p = prop, @v = val, @r = row_state 
	 from #props 
	where rowid = @index 
	
	if @r = 1
		EXEC sys.sp_addextendedproperty 
				@name = @p, 
				@value = @v,
				@level0type = N'SCHEMA', @level0name = @s, 
				@level1type = N'TABLE',  @level1name = @t
	if @r = 2
		EXEC sys.sp_updateextendedproperty
				@name = @p, 
				@value = @v,
				@level0type = N'SCHEMA', @level0name = @s, 
				@level1type = N'TABLE',  @level1name = @t

	select @index = min(rowid) from #props where rowid > @index and row_state in (1,2)
end 
--
-- Report
--
select case row_state when -1 then 'Table not Found' 
					  when 0 then 'No change to value' 
					  when 1 then 'Added property'
					  when 2 then 'Updated property' else 'Unknown' end as Status,
					  count(row_state) as Number
  from #props
group by row_state

if exists (select 1 from #props where row_state = 2)
	select sch + '.' + tbl as table_name, col as column_name, prop as property,prev_val as old_value, val as new_value 
	  from #props 
	  where row_state = 2";


		const String DropTablePropertyScriptTemplate = @"
if object_id('tempdb..#props') is not null drop table #props

create table #props (rowid int identity(1,1), sch varchar(128), tbl varchar(128), prop nvarchar(128))
insert into #props (sch, tbl, prop)
-- insert rows here
{0}
--
-- exec loop
--
declare @s varchar(128), @t varchar(128), @p  nvarchar(128)
declare @index int
select @index = min(rowid) from #props

while (@index is not null)
begin
	select top 1 @s = sch, @t = tbl, @p = prop from #props where rowid = @index
if exists( select 1
			FROM  sys.extended_properties SEP
			INNER JOIN sys.tables TBL ON SEP.major_id = TBL.OBJECT_ID
			INNER JOIN sys.schemas SCH ON TBL.SCHEMA_ID = SCH.SCHEMA_ID
			WHERE SEP.CLASS = 1 AND SEP.minor_id = 0 AND (SEP.value <> '1' AND SEP.value <> 1)
			  and SCH.name = @s and TBL.name = @t and SEP.name = @p)
EXEC sys.sp_dropextendedproperty 
			@name = @p, 
			@level0type = N'SCHEMA', @level0name = @s, 
			@level1type = N'TABLE',  @level1name = @t;

	select @index = min(rowid) from #props where rowid > @index
end";

		const String DropTableColumnPropertyScriptTemplate = @"
if object_id('tempdb..#props') is not null drop table #props

create table #props (rowid int identity(1,1), sch varchar(128), tbl varchar(128), col  varchar(128), prop nvarchar(128))
insert into #props (sch, tbl, col, prop)
-- insert rows here
{0}

--
-- exec loop
--
declare @s varchar(128), @t varchar(128), @c  varchar(128), @p  nvarchar(128)
declare @index int

select @index = min(rowid) from #props

while (@index is not null)
begin
	select top 1 @s = sch, @t = tbl, @c = col, @p = prop from #props where rowid = @index
-- check property exists
if exists( select 1 
			FROM  sys.extended_properties SEP
			INNER JOIN sys.columns COL ON SEP.major_id = COL.OBJECT_ID AND SEP.minor_id = COL.column_id
			INNER JOIN sys.tables TBL ON SEP.major_id = TBL.OBJECT_ID
			INNER JOIN sys.schemas SCH ON TBL.SCHEMA_ID = SCH.SCHEMA_ID
			WHERE SEP.CLASS = 1 AND (SEP.value <> '1' AND SEP.value <> 1)
			  and SCH.name = @s and TBL.name = @t and col.NAME = @c and SEP.name = @p)
EXEC sys.sp_dropextendedproperty 
			@name = @p, 
			@level0type = N'SCHEMA', @level0name = @s, 
			@level1type = N'TABLE',  @level1name = @t,
			@level2type = N'COLUMN', @level2name = @c;

	select @index = min(rowid) from #props where rowid > @index
end ";
	}
}

